package com.wondersgroup.tjfx.common;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.exception.ServiceException;
import com.wondersgroup.tjfx.bo.inter.sqlExecuter.ISqlExecuter;
import com.wondersgroup.tjfx.dao.inter.YdzgDataSqlBeanDao;
import jxl.CellView;
import jxl.Range;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * Created by WEI on 2017/06/30.
 */
@Controller
@RequestMapping("/datagridExport")
public class DatagridExportUtils {


    private static Logger logger = LoggerFactory.getLogger(DatagridExportUtils.class);

    @Autowired
    private ISqlExecuter sqlExecuter;

    @Autowired
    YdzgDataSqlBeanDao ydzgDataSqlBeanDao;

    private static final double iMaxLines = 6000;

    /**
     * easyui的数据表格导出excel
     */
    @RequestMapping("/easyui")
    public void easyuiDatagridExport(HttpServletRequest request, HttpServletResponse response) throws Exception {
        /**获得标题*/
        String title = request.getParameter("title");
        String fileName = title + ".xls";

        String settingStr = request.getParameter("settingStr");
        JSONArray setting = JSON.parseArray(settingStr);
        /**查询所有数据*/
        JSONArray data = queryData(request);

        //设置响应信息
        response.reset();// 清空输出流
        response.setCharacterEncoding("UTF-8");
        OutputStream out = null;
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));// 设定输出文件头
            out = response.getOutputStream();
            double totalLines = data.size();
            int page = (int) Math.ceil(totalLines / iMaxLines);
            WritableWorkbook book = Workbook.createWorkbook(out);
            for (double p = 0; p < page; p++) {
                WritableSheet sheet = book.createSheet(title + p, (int) p);
                String[] headerCol = writeHeader(sheet, setting, data);
                writeData(sheet, headerCol, data, setting.size(), p);
            }
            book.write();
            book.close();
            out.flush();
            out.close();
        } catch (IOException e) {
            logger.error(e.getMessage());
        } catch (WriteException e) {
            logger.error(e.getMessage());
        } finally {
            if (out != null)
                try {
                    out.close();
                } catch (IOException e) {
                    logger.error(e.getMessage());
                }
        }

    }

    private JSONArray queryData(HttpServletRequest request) throws Exception {
        String sql = request.getParameter("sql");
        sql = URLDecoder(sql);
        String params = request.getParameter("params");
        String sqlRes = "";
        Map<String, String> param = JSONObject.parseObject(params, Map.class);
        sqlRes = sqlExecuter.queryBySql(sql, "datagrid", param);
        JSONObject resultJson = JSONObject.parseObject(sqlRes);
        return resultJson.getJSONArray("data");
    }

    /**
     * 将表头写入excel中
     *
     * @param sheet
     * @param setting
     */
    private String[] writeHeader(WritableSheet sheet, JSONArray setting, JSONArray data) throws WriteException {
        CellInfo[][] cellInfos = parse(setting);
        String[] headerCol = new String[cellInfos[0].length];
        for (int i = 0; i < cellInfos.length; i++) {
            for (int j = 0; j < cellInfos[i].length; j++) {
                CellInfo cellInfo = cellInfos[i][j];
                if (cellInfo.isMerge)
                    continue;
                int rowspan = cellInfo.rowspan;
                int colspan = cellInfo.colspan;
                String title = cellInfo.text;
                String field = cellInfo.field;
                boolean isLastRow = cellInfos.length == i + 1;
                boolean isRowspanToLastRow = cellInfos.length == i + rowspan;
                if (isLastRow || isRowspanToLastRow) {
                    headerCol[cellInfo.y] = field;
                }
                if (colspan > 1 || rowspan > 1) {
                    sheet.mergeCells(j, i, j + colspan - 1, i + rowspan - 1);
                }
                WritableCellFormat cellFormat = new WritableCellFormat();
                cellFormat.setWrap(true);//列宽自适应
                cellFormat.setAlignment(Alignment.CENTRE);//文字居中
                Label label = new Label(j, i, title);
                label.setCellFormat(cellFormat);
                sheet.addCell(label);
            }
        }
        return headerCol;
    }


    /**
     * 写入数据
     *
     * @param sheet
     * @param headerCol
     * @param data
     * @param size
     * @param page
     * @throws WriteException
     */
    private void writeData(WritableSheet sheet, String[] headerCol, JSONArray data, int size, double page) throws WriteException {
        int dataSize = data.size();
        int colSize = headerCol.length;
        double start = page * iMaxLines;
        double end = (page + 1) * iMaxLines;
        int temp = 0;
        for (double i = start; i < dataSize && i < end; i++) {
            JSONObject row = data.getJSONObject((int) i);
            for (int j = 0; j < colSize; j++) {
                String filed = headerCol[j];
                String value = row.getString(filed);
                Label label = null;
                if (page >= 1) {
                    label = new Label(j, size + temp, value);
                } else {
                    label = new Label(j, size + (int) i, value);
                }
                sheet.addCell(label);
            }
            temp++;
        }
    }

    /**
     * 解码
     */
    private String URLDecoder(String sql) {
        try {
            sql = URLDecoder.decode(sql, "utf-8");
            sql = URLDecoder.decode(sql, "utf-8");
        } catch (Exception e) {
//			e.printStackTrace();
        }
        return sql;
    }

    private CellInfo[][] parse(JSONArray columns) {
        int size = columns.size();
        int colSize = getColSize(columns.getJSONArray(0));
        CellInfo[][] cellInfos = new CellInfo[size][colSize];
        for (int i = 0; i < size; i++) {
            for (int j = 0; j < colSize; j++) {
                cellInfos[i][j] = new CellInfo(i, j);
            }
        }
        for (int i = 0; i < size; i++) {
            JSONArray column = columns.getJSONArray(i);//[{field:"",colspan:1,rowspan:1},...]
            int length = column.size();
            for (int j = 0; j < length; j++) {
                JSONObject col = column.getJSONObject(j);//{field:"",colspan:1,rowspan:1}
                Integer colspan = 1, rowspan = 1;
                if (col.containsKey("colspan")) {
                    colspan = col.getInteger("colspan");
                }
                if (col.containsKey("rowspan")) {
                    rowspan = col.getInteger("rowspan");
                }
                CellInfo cellInfo = fixCell(cellInfos, i, j);
                cellInfo.setColspan(colspan);
                cellInfo.setRowspan(rowspan);
                cellInfo.setText(col.getString("title"));
                cellInfo.setField(col.getString("field"));
                //
                if (colspan + rowspan > 2) {
                    setNull(cellInfo, cellInfos);
                }
            }
        }
        return cellInfos;
    }

    private int getColSize(JSONArray column) {
        int size = column.size();
        int colSize = 0;
        for (int i = 0; i < size; i++) {
            JSONObject col = column.getJSONObject(i);
            if (col.containsKey("colspan")) {
                Integer colspan = col.getInteger("colspan");
                colSize += colspan;
            } else {
                colSize++;
            }
        }
        return colSize;
    }

    private void setNull(CellInfo cellInfo, CellInfo[][] cellInfos) {
        int colspan = cellInfo.getColspan();
        int rowspan = cellInfo.getRowspan();
        for (int i = 0; i < rowspan; i++) {
            for (int j = 0; j < colspan; j++) {
                if (i == 0 && j == 0) {
                    continue;
                }
                int x = cellInfo.getX();
                int y = cellInfo.getY();
                CellInfo cell = cellInfos[i + x][j + y];
                cell.setMerge(true);
            }
        }
    }

    private CellInfo fixCell(CellInfo[][] cellInfos, int x, int y) {
        CellInfo[] cells = cellInfos[x];
        int length = cells.length;
        int index = 0;
        for (int i = 0; i < length; i++) {
            CellInfo cell = cells[i];
            if (cell.isMerge) {
                continue;
            }
            if (index == y) {
                return cell;
            }
            index++;
        }
        throw new ServiceException("ERROR", "解析配置错误!");
    }

    class CellInfo {
        private int x;
        private int y;
        private int colspan;
        private int rowspan;
        private String text;
        private String field;
        private boolean isMerge;

        public CellInfo(int x, int y) {
            this.x = x;
            this.y = y;
            this.colspan = 1;
            this.rowspan = 1;
            this.text = "";
            this.field = "";
        }

        public int getX() {
            return x;
        }

        public void setX(int x) {
            this.x = x;
        }

        public int getY() {
            return y;
        }

        public void setY(int y) {
            this.y = y;
        }

        public int getColspan() {
            return colspan;
        }

        public void setColspan(int colspan) {
            this.colspan = colspan;
        }

        public int getRowspan() {
            return rowspan;
        }

        public void setRowspan(int rowspan) {
            this.rowspan = rowspan;
        }

        public String getText() {
            return text;
        }

        public void setText(String text) {
            this.text = text;
        }

        public String getField() {
            return field;
        }

        public void setField(String field) {
            this.field = field;
        }

        public boolean isMerge() {
            return isMerge;
        }

        public void setMerge(boolean merge) {
            isMerge = merge;
        }
    }

}
